/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;


import java.sql.*;
import java.util.ArrayList;


/**
 *
 * @author Lie
 */
public abstract class DBExtentions {

       private  Connection con;


    public DBExtentions() {
      con = DbConnection.getInstance().getDBcon();
    }

    public int rollBack(ArrayList<String> queryList)
    {
        int rc=-1;
        try{ // delete objekt
                    con.setAutoCommit(false);
                    Statement stmt = con.createStatement();
                    stmt.setQueryTimeout(5);
                    for(String query : queryList){
                        rc = stmt.executeUpdate(query);
                    }
                    
                    con.commit();
                    stmt.close();
                    con.setAutoCommit(true);

  		}//end try
                catch(SQLException ex){
                    System.out.println("Transaktion is not done ");
                    System.out.println("exception in Object db: "+ex);
                    try{
                        con.rollback();
                        con.setAutoCommit(true);
                        System.out.println("Rolling back!");
                    }
                    catch(Exception ex1)
                    {
                        System.out.println("Error rolling back"+ ex1);
                    }
                }
    return rc;
    }

    public String getCity(int zip)
    {
    ResultSet results;
    String city = "";
    String query="select * From ZipCity where zip = "+ zip;
    try{
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        results = stmt.executeQuery(query);
        if( results.next() ){
            city = results.getString("City");
        }
    }
    catch(Exception e){
        System.out.println("Query exception: "+e);
	}
    return city;
}
    public int getCityID(int zip)
    {
    ResultSet results;
    int cityId = 0;
    String query="select * From ZipCity where zip = "+ zip;
    try{
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        results = stmt.executeQuery(query);
        if( results.next() ){
            cityId = results.getInt("CityID");
        }
    }
    catch(Exception e){
        System.out.println("Query exception: "+e);
	}
    return cityId;
    }

    public int getZipCode(int CityID)
    {
    ResultSet results;
    int ZipCode = 0;
    String query="select * From ZipCity where cityID = "+ CityID;
    try{
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        results = stmt.executeQuery(query);
        if( results.next() ){
            ZipCode = results.getInt("zip");
        }
    }
    catch(Exception e){
        System.out.println("Query exception: "+e);
	}
    return ZipCode;
    }



    public String getCountry(String cCode)
    {
    ResultSet results;
    String contryName = "";
    String query="select * From Country where contryCode = "+ cCode;
    try{
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        results = stmt.executeQuery(query);
        if( results.next() ){
            contryName = results.getString("contryName");
        }
    }
    catch(Exception e){
        System.out.println("Query exception: "+e);
	}
    return contryName;
}
    public int getContryId(String cCode)
    {
    ResultSet results;
    int contryId = 0;
    String query="select * From Contry where contryCode = "+ cCode;
    try{
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        results = stmt.executeQuery(query);
        if( results.next() ){
            contryId = results.getInt("countyID");
        }
    }
    catch(Exception e){
        System.out.println("Query exception: "+e);
	}
    return contryId;
    }



}
